/************************************************************************************/
/*  Program name CAPBRIMP_MAPPING.sql                                               */
/*  Creater:  Francis Lee (PCCW - UniHub)                                           */
/*************   History   *************************                                */
/* 10 Feb, 2005   Francis Lee         First created                                 */
/************************************************************************************/

SET DEFINE OFF;
CREATE OR REPLACE PACKAGE CAPPOIMP_MAPPING
IS
	FUNCTION GetVendorIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetVendorSiteIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetVendorContactIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetItemIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetHRLocationIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetPOHeaderIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetPOLineIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetPODistributionIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetPOLineLocationIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetPRHeaderIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetPRLineIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetPRDistributionIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetReceiptHeaderIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetReceiptLineIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetReceiptTransactionIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetPositionIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetPosStructureMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetOrgStructureMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetOrgStructureVersionMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetJOBIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetControlGroupIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
END CAPPOIMP_MAPPING;
/
SET DEFINE OFF;

CREATE OR REPLACE PACKAGE BODY CAPPOIMP_MAPPING
IS
/********************************************************/
	FUNCTION GetVendorIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			select vendor_id_n
			into   lv_NewID
			from   crst_po_vendors
			where  vendor_id = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
		END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetVendorIDMapping;
/********************************************************/
	FUNCTION GetVendorSiteIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			select vendor_site_id_n
			into   lv_NewID
			from   crst_po_vendor_sites_all
			where  vendor_site_id = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
				lv_NewID := pOldID;
		END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetVendorSiteIDMapping;
/********************************************************/
	FUNCTION GetVendorContactIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			select vendor_contact_id_n
			into   lv_NewID
			from   crst_po_vendor_contacts
			where  vendor_contact_id = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
				lv_NewID := pOldID;
		END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetVendorContactIDMapping;
/********************************************************/
    FUNCTION GetItemIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			SELECT	INVENTORY_ITEM_ID_N
			INTO	lv_NewID
			FROM	CRST_MTL_SYSTEM_ITEMS_B
			WHERE	INVENTORY_ITEM_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetItemIDMapping;
/********************************************************/
	FUNCTION GetHRLocationIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			SELECT	LOCATION_ID_N
			INTO	lv_NewID
			FROM	CRST_HR_LOCATIONS_ALL
			WHERE	LOCATION_ID	= pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := NULL;
			END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetHRLocationIDMapping;
/********************************************************/
	FUNCTION GetPOHeaderIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL 	THEN
			BEGIN
			SELECT	PO_HEADER_ID_N
			INTO	lv_NewID
			FROM	CRST_PO_HEADERS_ALL
			WHERE	PO_HEADER_ID = pOldID;
			EXCEPTION
					WHEN NO_DATA_FOUND THEN
						lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetPOHeaderIDMapping;
/********************************************************/
	FUNCTION GetPOLineIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL 	THEN
			BEGIN
			SELECT	PO_LINE_ID_N
			INTO	lv_NewID
			FROM	CRST_PO_LINES_ALL
			WHERE	PO_LINE_ID = pOldID;
			EXCEPTION
					WHEN NO_DATA_FOUND THEN
						lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetPOLineIDMapping;
/********************************************************/
	FUNCTION GetPODistributionIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			SELECT	PO_DISTRIBUTION_ID_N
			INTO	lv_NewID
			FROM	CRST_PO_DISTRIBUTIONS_ALL
			WHERE	PO_DISTRIBUTION_ID	= pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetPODistributionIDMapping;
/********************************************************/
	FUNCTION GetPOLineLocationIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	LINE_LOCATION_ID_N
			INTO	lv_NewID
			FROM	CRST_PO_LINE_LOCATIONS_ALL
			WHERE	LINE_LOCATION_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetPOLineLocationIDMapping;
/********************************************************/
	FUNCTION GetPRHeaderIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			SELECT	REQUISITION_HEADER_ID_N
			INTO	lv_NewID
			FROM	CRST_PO_REQUISITION_HEADERS_AL
			WHERE	REQUISITION_HEADER_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetPRHeaderIDMapping;
/********************************************************/
	FUNCTION GetPRLineIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	REQUISITION_LINE_ID_N
			INTO	lv_NewID
			FROM	CRST_PO_REQUISITION_LINES_ALL
			WHERE	REQUISITION_LINE_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetPRLineIDMapping;	
/********************************************************/
	FUNCTION GetPRDistributionIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	DISTRIBUTION_ID_N
			INTO	lv_NewID
			FROM	CRST_PO_REQ_DISTRIBUTIONS_ALL
			WHERE	DISTRIBUTION_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetPRDistributionIDMapping;
/********************************************************/
	FUNCTION GetReceiptHeaderIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	SHIPMENT_HEADER_ID_N
			INTO	lv_NewID
			FROM	CRST_RCV_SHIPMENT_HEADERS 
			WHERE	SHIPMENT_HEADER_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetReceiptHeaderIDMapping;			
/********************************************************/
	FUNCTION GetReceiptLineIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	SHIPMENT_LINE_ID_N
			INTO	lv_NewID
			FROM	CRST_RCV_SHIPMENT_LINES 
			WHERE	SHIPMENT_LINE_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetReceiptLineIDMapping;			
/********************************************************/
	FUNCTION GetReceiptTransactionIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	TRANSACTION_ID_N
			INTO	lv_NewID
			FROM	CRST_RCV_TRANSACTIONS
			WHERE	TRANSACTION_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetReceiptTransactionIDMapping;			
/********************************************************/
	FUNCTION GetJOBIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID	NUMBER;
	BEGIN
		IF pOldID IS NOT NULL THEN
			BEGIN
				SELECT JOB_ID_N
				INTO lv_NewID
				FROM CRST_PER_JOBS
				WHERE JOB_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID := NULL;
		END IF;
		RETURN(lv_Newid);
	END GetJOBIDMapping;
/********************************************************/
	FUNCTION GetPositionIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID	NUMBER;
	BEGIN
		IF pOldID IS NOT NULL THEN
			BEGIN
				SELECT POSITION_ID_N
				INTO lv_NewID
				FROM CRST_PER_ALL_POSITIONS
				WHERE POSITION_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID := NULL;
		END IF;
		RETURN(lv_Newid);
	END GetPositionIDMapping;
/********************************************************/
FUNCTION GetPosStructureMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID 	NUMBER;
	BEGIN
		IF pOldID IS NOT NULL THEN
			BEGIN
				SELECT POSITION_STRUCTURE_ID_N
				INTO lv_NewID
				FROM CRST_PER_POSITION_STRUCTURES
				WHERE POSITION_STRUCTURE_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID := NULL;
		END IF;
		RETURN(lv_Newid);
	END GetPosStructureMapping;
/********************************************************/
FUNCTION GetOrgStructureMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID	NUMBER;
	BEGIN
		IF pOldID IS NOT NULL THEN
			BEGIN
				SELECT ORGANIZATION_STRUCTURE_ID_N
				INTO lv_NewID
				FROM CRST_PO_ORGANIZATION_STRUCTURE
				WHERE ORGANIZATION_STRUCTURE_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID := NULL;
		END IF;
		RETURN(lv_Newid);
	END GetOrgStructureMapping;
/********************************************************/
/********************************************************/
FUNCTION GetOrgStructureVersionMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID	NUMBER;
	BEGIN
		IF pOldID IS NOT NULL THEN
			BEGIN
				SELECT ORG_STRUCTURE_VERSION_ID_N
				INTO lv_NewID
				FROM CRST_PO_ORG_STRUCT_VERSION
				WHERE ORG_STRUCTURE_VERSION_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID := NULL;
		END IF;
		RETURN(lv_Newid);
	END GetOrgStructureVersionMapping;
/********************************************************/
/********************************************************/
FUNCTION GetControlGroupIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID	NUMBER;
	BEGIN
		IF pOldID IS NOT NULL THEN
			BEGIN
				SELECT CONTROL_GROUP_ID_N
				INTO lv_NewID
				FROM CRST_PO_CONTROL_GROUP
				WHERE CONTROL_GROUP_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID := NULL;
		END IF;
		RETURN(lv_Newid);
	END GetControlGroupIDMapping;
/********************************************************/
END CAPPOIMP_MAPPING;
/
